package org.lanqiao.dao;

import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.lanqiao.Db.DBDriverFactory;
import org.lanqiao.bean.EduManager;
import org.lanqiao.bean.EduStuCourseSel;
import org.lanqiao.bean.EduTeacherBean;
import org.lanqiao.bean.EduUserBean;
import org.lanqiao.bean.GiveGradeBean;
import org.lanqiao.bean.StuCourse;
import org.lanqiao.bean.TeaCourse;

/**
 * @author loveli
 *
 */
public class UserInfoManagerDao {

	/**
	 * 把信息存到数据库中，返回整数
	 * 
	 * @param eub
	 * @return
	 */
	public int saveUser(EduUserBean eub) {
		Integer userId = eub.getUserid();
		int result = 0;
		String strSql = "";
		if (userId == 0) {
			// 正常的插入查询结果返回
			strSql = "INSERT INTO EDUUSER(USERNAME,USERPASSWORD,USERAGE,USERSEX,USERTEL,USERADRESS)"
					+ "VALUES(?,?,?,?,?,?)";

			result = DBDriverFactory.getDbDriverInstance().update(strSql, new Object[] { eub.getUsername(),
					eub.getUserpassword(), eub.getUserage(), eub.getUsersex(), eub.getUsertel(), eub.getUseradress() });
		} else {
			strSql = "UPDATE EDUUSER SET USERNAME=?,USERPASSWORD=?,USERAGE=?,USERSEX=?,USERTEL=?,USERADRESS=? "
					+ "WHERE USERID=? ";
			result = DBDriverFactory.getDbDriverInstance().update(strSql,
					new Object[] { eub.getUsername(), eub.getUserpassword(), eub.getUserage(), eub.getUsersex(),
							eub.getUsertel(), eub.getUseradress(), eub.getUserid() });
		}
		// sql语句

		return result;
	}

	public List returnList(EduUserBean eub) {
		// 逆序数据信息
		String strSql = "SELECT * FROM EDUUSER ORDER BY USERID DESC";
		List listUser = DBDriverFactory.getDbDriverInstance().query(strSql, new BeanListHandler<>(EduUserBean.class));
		return listUser;
	}

	public EduUserBean getUserInfoModifyDb(String userid) {
		// 写数据库的sql语句，从数据库中获取数据
		String strSql = "SELECT * FROM EDUUSER WHERE USERID=?";
		EduUserBean userBean = DBDriverFactory.getDbDriverInstance().query(strSql, new BeanHandler<>(EduUserBean.class),
				new Object[] { userid });
		return userBean;
	}

	public List doQuery(EduUserBean eub) {
		String strSql = "SELECT * FROM EDUUSER WHERE 1=1";
		// 创建一个集合来怎么存放
		List<Object> param = new ArrayList<Object>();
		// 判断是不是有姓名查询
		if (eub.getUsername() != null && !eub.getUsername().trim().equals("")) {
			// 姓名查询
			strSql += " AND USERNAME like ?";

			param.add("%" + eub.getUsername() + "%");
		} else if (eub.getUsersex() != null) {
			// 验证是否有性别的查询
			strSql += " AND USERSEX=?";

			param.add(eub.getUsersex());
		} else if (eub.getUserage() > 0) {
			// 验证年龄段的小年龄
			strSql += " AND USERAGE>=?";

			param.add(eub.getUserage());
		} else if (eub.getUserage1() > 0) {
			// 验证年龄段的大年龄
			strSql += " AND USERAGE<=?";

			param.add(eub.getUserage1());
		} else if (eub.getUsertel() != null && !eub.getUsertel().trim().equals("")) {
			// 验证是否查询了电话相关信息
			strSql += " AND USERTEL LIKE ?";

			param.add("%" + eub.getUsertel() + "%");
		} else if (eub.getUseradress() != null && !eub.getUseradress().trim().equals("")) {
			// 验证是否查询查询了地址
			strSql += " AND USERADRESS LIKE ?";

			param.add("%" + eub.getUseradress() + "%");
		}

		List listQuery = (List) DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler(EduUserBean.class), param.toArray());

		return listQuery;
	}

	public int deletUserInfo(String strDel) {
		String strSql = "DELETE FROM EDUUSER WHERE USERID=?";
		int deleteRes = DBDriverFactory.getDbDriverInstance().update(strSql, new Object[] { strDel });
		return deleteRes;
	}

	public EduUserBean updateStudentInf(EduUserBean eub) {
		String strSql = "UPDATE EDUUSER SET USERNAME=?,USERPASSWORD=?,USERAGE=?,USERSEX=?,USERTEL=?,USERADRESS=?"
				+ " WHERE USERID=?";
		DBDriverFactory.getDbDriverInstance().update(strSql, new Object[] { eub.getUsername(), eub.getUserpassword(),
				eub.getUserage(), eub.getUsersex(), eub.getUsertel(), eub.getUseradress(), eub.getUserid() });

		String strSql1 = "SELECT * FROM EDUUSER WHERE USERID=?";
		EduUserBean result = DBDriverFactory.getDbDriverInstance().query(strSql1, new BeanHandler<>(EduUserBean.class),
				new Object[] { eub.getUserid() });
		return result;
	}

	public List getStuC(String userName) {
		String strSql = "SELECT EC.COURSEID,EC.COURSENAME,EC.TEACHERID,ET.TEACHERNAME "
				+ "FROM EDUCOURSE EC,EDUTEACHER ET WHERE EC.TEACHERID=ET.TEACHERID "
				+ "AND EC.COURSEID NOT IN(SELECT COURSEID FROM EDUSTUCOURSE WHERE USERNAME=?) ORDER BY EC.COURSEID";
		List escs = (List) DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler(EduStuCourseSel.class),new Object[]{userName});
		return escs;
	}

	public List getStuCoIn(String userName) {
		String strSql = "SELECT ES.USERNAME,ES.COURSEID,EC.COURSENAME,EC.TEACHERID,ET.TEACHERNAME,ES.COURSESCORE"
				+" FROM EDUSTUCOURSE ES,EDUCOURSE EC,EDUTEACHER ET WHERE ET.TEACHERID=EC.TEACHERID AND "
				+ "ES.COURSEID=EC.COURSEID AND ES.USERNAME=? ORDER BY COURSEID";
		List stuCourse=DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler<>(StuCourse.class),new Object[]{userName});
		return stuCourse;
	}

	public List getStuAllCInfo(String couseId, String stuName) {
		String strSql="INSERT INTO EDUSTUCOURSE(USERNAME,COURSEID) VALUES(?,?)";
		DBDriverFactory.getDbDriverInstance().update(strSql, 
				new Object[]{stuName,couseId});
		String strSql1 = "SELECT EC.COURSEID,EC.COURSENAME,EC.TEACHERID,ET.TEACHERNAME "
				+ "FROM EDUCOURSE EC,EDUTEACHER ET WHERE EC.TEACHERID=ET.TEACHERID "
				+ "AND EC.COURSEID NOT IN(SELECT COURSEID FROM EDUSTUCOURSE WHERE USERNAME=?)";
		List escs = (List) DBDriverFactory.getDbDriverInstance().query(strSql1,
				new BeanListHandler(EduStuCourseSel.class),new Object[]{stuName});
		return escs;
	}

	public EduManager getManagerInf(EduManager em) {
		String strSql="SELECT * FROM EDUMANAGER WHERE MANAGERNAME=? AND MANAGERPAW=?";
		EduManager emg=DBDriverFactory.getDbDriverInstance().query(strSql,new BeanHandler<>(EduManager.class),new Object[]{em.getManagername(),em.getManagerpaw()});
		return emg;
	}

	public int savaTeacherInfo(EduTeacherBean etb) {
		String strSql="INSERT INTO EDUTEACHER(TEACHERNAME,TEACHERPASSWORD,TEACHERAGE,TEACHERSEX,TEACHERTEL,TEACHERDRESS)"
                         +" VALUES(?,?,?,?,?,?)";
		
		return DBDriverFactory.getDbDriverInstance().update(strSql,
				new Object[]{etb.getTeachername(),etb.getTeacherpassword(),etb.getTeacherage(),etb.getTeachersex(),
						etb.getTeachertel(),etb.getTeacherdress()});
	}

	public List<EduTeacherBean> getTeacherListInfo(EduTeacherBean etb) {
		String strSql="SELECT * FROM EDUTEACHER ORDER BY TEACHERID DESC";
		return DBDriverFactory.getDbDriverInstance().query(strSql, new BeanListHandler<>(EduTeacherBean.class));
	}

	public List getTeaQueryListInfo(EduTeacherBean eub) {
		String strSql = "SELECT * FROM EDUTEACHER WHERE 1=1";
		// 创建一个集合来怎么存放
		List<Object> param = new ArrayList<Object>();
		// 判断是不是有姓名查询
		if (eub.getTeachername() != null && !eub.getTeachername().trim().equals("")) {
			// 姓名查询
			strSql += " AND TEACHERNAME LIKE ?";

			param.add("%" + eub.getTeachername() + "%");
		} else if (eub.getTeachersex() != null) {
			// 验证是否有性别的查询
			strSql += " AND TEACHERSEX=?";

			param.add(eub.getTeachersex());
		} else if (eub.getTeacherage() > 0) {
			// 验证年龄段的小年龄
			strSql += " AND TEACHERAGE>=?";

			param.add(eub.getTeacherage());
		} else if (eub.getTeacherage1() > 0) {
			// 验证年龄段的大年龄
			strSql += "  AND TEACHERAGE<=?";

			param.add(eub.getTeacherage1());
		} else if (eub.getTeachertel() != null && !eub.getTeachertel().trim().equals("")) {
			// 验证是否查询了电话相关信息
			strSql += " AND TEACHERTEL LIKE ?";

			param.add("%" + eub.getTeachertel() + "%");
		} else if (eub.getTeacherdress() != null && !eub.getTeacherdress().trim().equals("")) {
			// 验证是否查询查询了地址
			strSql += " AND TEACHERDRESS LIKE ?";

			param.add("%" + eub.getTeacherdress() + "%");
		}

		List listQuery = (List) DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler(EduTeacherBean.class), param.toArray());

		return listQuery;
	}

	public List getTeacherCourse(String userName) {
		//获取老师的课程的信息
		String strSql="SELECT ET.TEACHERNAME,EC.COURSEID,EC.COURSENAME "
				+ "FROM EDUCOURSE EC,EDUTEACHER ET WHERE EC.TEACHERID=ET.TEACHERID AND ET.TEACHERNAME=?";
		List tcl=DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler<>(TeaCourse.class),new Object[]{userName});
		return tcl;
	}

	public List getStucInf(String userName, String tc) {
		String strSql="SELECT DISTINCT ES.USERNAME,ES.COURSEID,EC.COURSENAME,ES.COURSESCORE" 
                +" FROM EDUSTUCOURSE ES,EDUCOURSE EC"
                +" WHERE ES.COURSEID=EC.COURSEID AND ES.COURSEID IN("
                +" SELECT EC.COURSEID FROM EDUCOURSE EC,EDUTEACHER ET WHERE"
                +" ET.TEACHERID=EC.TEACHERID AND ET.TEACHERNAME=?)  AND COURSENAME=? ORDER BY COURSEID";
		List stuCI=DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler<>(GiveGradeBean.class),new Object[]{userName,tc});
		return stuCI;
	}

	public int updateStudentScore(List scList) {
		Iterator it = scList.iterator();
		while (it.hasNext()) {
			StuCourse sc=(StuCourse) it.next();
			String strSql = "UPDATE EDUSTUCOURSE SET COURSESCORE=? WHERE USERNAME=? AND COURSEID=?";
			DBDriverFactory.getDbDriverInstance().update(strSql, new Object[] {sc.getCoursescore(),sc.getUsername(),sc.getCourseid()});
		}
		return 1;
	}

	public List getAllCourse() {
		String strSql="SELECT * FROM EDUALLCOURSE";
		List tc=DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler<>(TeaCourse.class));
		return tc;
	}

	public List getOtherAllCou() {
		String strSql="SELECT * FROM EDUALLCOURSE WHERE COURSEID NOT IN("
+"SELECT COURSEID FROM EDUCOURSE)";
		List toca=DBDriverFactory.getDbDriverInstance().query(strSql, 
				new BeanListHandler<>(TeaCourse.class));
		return toca;
	}

	public List getAotherCourse(String couId, String couName, String teaId) {
		String strSql="INSERT INTO EDUCOURSE(COURSEID,COURSENAME,TEACHERID)VALUES(?,?,?)";
		DBDriverFactory.getDbDriverInstance().update(strSql, new Object[]{couId,couName,teaId});
		String strSql1="SELECT * FROM EDUALLCOURSE WHERE COURSEID NOT IN("
				+"SELECT COURSEID FROM EDUCOURSE)";
	   List toca=DBDriverFactory.getDbDriverInstance().query(strSql1, 
			new BeanListHandler<>(TeaCourse.class));
		return toca;
	}

	public EduTeacherBean getTeaSelfInfo(EduTeacherBean etb) {
		String strSql="UPDATE EDUTEACHER SET TEACHERNAME=?,TEACHERPASSWORD=?,TEACHERAGE=?,TEACHERSEX=?,"
				+ "TEACHERTEL=?,TEACHERDRESS=?"
                     +" WHERE TEACHERID=?";
		DBDriverFactory.getDbDriverInstance().update(strSql,
				new Object[]{etb.getTeachername(),etb.getTeacherpassword(),etb.getTeacherage(),etb.getTeachersex(),
						etb.getTeachertel(),etb.getTeacherdress(),etb.getTeacherid()});
		String strSql1="SELECT * FROM EDUTEACHER WHERE TEACHERID=?";
		EduTeacherBean etbb=(EduTeacherBean) DBDriverFactory.getDbDriverInstance().query(strSql1,
				new BeanHandler<>(EduTeacherBean.class),new Object[]{etb.getTeacherid()});
		return etbb;
	}

	public List insertcourse(String couid, String couname) {
		String strSql="INSERT INTO EDUALLCOURSE(COURSEID,COURSENAME)VALUES(?,?)";
		DBDriverFactory.getDbDriverInstance().update(strSql, new Object[]{couid,couname});
		String strSql1="SELECT * FROM EDUALLCOURSE ORDER BY COURSEID DESC";
		List coulist=DBDriverFactory.getDbDriverInstance().query(strSql1,
				new BeanListHandler<>(TeaCourse.class));
		return coulist;
	}

	public List getATC(Integer teacherid) {
		String strSql="SELECT * FROM EDUCOURSE WHERE TEACHERID=?";
	    List lis=DBDriverFactory.getDbDriverInstance().query(strSql,
	    		new BeanListHandler<>(TeaCourse.class),new Object[]{teacherid});
		return lis;
	}

	public void deleteStuCourse(String cousid, String stuName) {
		String strSql="DELETE FROM EDUSTUCOURSE WHERE USERNAME=? AND COURSEID=?";
		DBDriverFactory.getDbDriverInstance().delete(strSql, new Object[]{stuName,cousid});
	}

	public List getNowTeaCourse(String teaId) {
		//获取当前老师的课程的sql语句
		String strSql="SELECT * FROM EDUCOURSE WHERE TEACHERID=?";
		List lis=(List) DBDriverFactory.getDbDriverInstance().query(strSql,
				new BeanListHandler(TeaCourse.class),new Object[]{teaId});
		return lis;
	}

	public void deleteTeaCouse(String strCid) {
		String strSql="DELETE FROM EDUCOURSE WHERE COURSEID=?";
		DBDriverFactory.getDbDriverInstance().delete(strSql, new Object[]{strCid});
	}

}
